package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.Beans.CompetitionDetails;
import cosplay.Beans.Users;

@Repository ("userdao")
public class UserDAOImpl implements UserDAO{
	@Autowired
	private JdbcTemplate jt;

	@Override
	public int add(final Users user) {
		// TODO Auto-generated method stub
		int id=0;			
		if(user!=null)
		{
			if (user.getNickName()==null)
				user.setNickName(user.getUserName());
			if (user.getUserRole()==null)
				user.setUserRole("ROLE_USER");
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_ADD(?,?,?,?,?,?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, user.getUserName());
							stm.setString(2, user.getPassWord());
							stm.setString(3, user.getDescription());
							stm.setString(4, user.getEmail());
							stm.setBytes(5, user.getHeadImage());
							stm.setString(6, user.getCountry());
							stm.setString(7, user.getAddress());
							stm.setString(8, user.getTel());
							stm.setDate(9, new java.sql.Date(user.getBirthday().getTime()));
							stm.setString(10, user.getNickName());
							stm.setString(11, user.getUserRole());
							stm.setString(12, user.getGender());
							stm.registerOutParameter(13, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(13);
								obj[1]=rs;
								return obj;
							}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delete(final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(id>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_DELETE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=rs;
								return obj;
							}});
			sucess=true;
		}
		return sucess;
	}
	
	@Override
	public boolean edit(final Users user, final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if((user!=null)&&(id>0))
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_EDIT(?,?,?,?,?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.setString(2, user.getPassWord());
							stm.setString(3, user.getDescription());
							stm.setString(4, user.getEmail());
							stm.setBytes(5, user.getHeadImage());
							stm.setString(6, user.getCountry());
							stm.setString(7, user.getAddress());
							stm.setString(8, user.getTel());
							stm.setDate(9, new java.sql.Date(user.getBirthday().getTime()));
							stm.setString(10, user.getNickName());
							stm.setString(11, user.getUserRole());
							stm.setString(12, user.getGender());
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=rs;
								return obj;
							}});
			sucess=true;
		}
		return sucess;
	}

	@Override
	public Users get(final int id) {
		// TODO Auto-generated method stub
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GET(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								Users user =new Users();
								if (rs.next())
								{
									user.setAddress(rs.getString("address"));
									user.setBirthday(rs.getDate("birthday"));
									user.setCountry(rs.getString("country"));
									user.setDescription(rs.getString("description"));
									user.setEmail(rs.getString("email"));
									user.setHeadImage(rs.getBytes("headimage"));
									user.setId(rs.getInt("id"));
									user.setLastLogIn(rs.getTimestamp("lastlogin"));
									user.setLastLogIP(rs.getString("lastlogip"));
									user.setNickName(rs.getString("nickname"));
									user.setPassWord(rs.getString("password"));
									user.setRegisterd(rs.getTimestamp("registerd"));
									user.setTel(rs.getString("tel"));
									user.setUserName(rs.getString("username"));
									user.setUserRole(rs.getString("userrole"));
									user.setCompetitionID(rs.getInt("competitionid"));
									user.setCity("city");
									user.setGender("male");
									obj[1]=user;
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (Users) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getAll() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL USER_GETALL(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Integer> users=new ArrayList<Integer>();
							while (rs.next())
							{
								users.add(rs.getInt("id"));
							}
							obj[1]=users;
							return obj;
						}});
		return (List<Integer>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getLastJoined() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL USER_GETLASTJOINED();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> users=new ArrayList<Integer>();
							while (rs.next())
							{
								users.add(rs.getInt("id"));
							}
							obj[1]=users;
							return obj;
						}});
		return (List<Integer>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getLastLog() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL USER_GETLASTLOGGED();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> users=new ArrayList<Integer>();
							while (rs.next())
							{
								users.add(rs.getInt("id"));
							}
							obj[1]=users;
							return obj;
						}});
		return (List<Integer>) obj[1];
	}
	
	@Override
	public int verify(final String uname) {
		// TODO Auto-generated method stub
		if(uname!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_VERIFY(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, uname);
							stm.registerOutParameter(2, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Integer) obj[0]).intValue();
		}
		return 3;
	}

	@Override
	public boolean login(final int userid,final String logip) {
		// TODO Auto-generated method stub
		if((userid>0)&&(logip!=null))
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_LOGIN(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, userid);
							stm.setString(2, logip);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

	@Override
	public Users getByName(final String uname) {
		// TODO Auto-generated method stub
		if(uname!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GETBYNAME(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, uname);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								Users user =new Users();
								if (rs.next())
								{
									user.setAddress(rs.getString("address"));
									user.setBirthday(rs.getDate("birthday"));
									user.setCountry(rs.getString("country"));
									user.setDescription(rs.getString("description"));
									user.setEmail(rs.getString("email"));
									user.setHeadImage(rs.getBytes("headimage"));
									user.setId(rs.getInt("id"));
									user.setLastLogIn(rs.getTimestamp("lastlogin"));
									user.setLastLogIP(rs.getString("lastlogip"));
									user.setNickName(rs.getString("nickname"));
									user.setPassWord(rs.getString("password"));
									user.setRegisterd(rs.getTimestamp("registerd"));
									user.setTel(rs.getString("tel"));
									user.setUserName(rs.getString("username"));
									user.setUserRole(rs.getString("userrole"));
									user.setCompetitionID(rs.getInt("competitionid"));
									user.setCity("city");
									user.setGender("male");
									obj[1]=user;
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (Users) obj[1];
		}
		return null;
	}

	@Override
	public byte[] getHeadImage(final int userid) {
		// TODO Auto-generated method stub
		if(userid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GETHEADIMAGE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, userid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("headimage");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}

	@Override
	public boolean join(final CompetitionDetails detail) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(detail!=null)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_JOIN(?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, detail.getUserID());
							stm.setInt(2, detail.getCompetitionID());
							stm.setString(3, detail.getCharacter());
							stm.setString(4, detail.getCostume());
							stm.setString(5, detail.getPhotographer());
							stm.setString(6, detail.getDescription());
							stm.setString(7, detail.getType());
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=rs;
								return obj;
							}});
			sucess=true;
		}
		return sucess;
	}

	@Override
	public boolean quit(final int uid, final int cid) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(uid>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_QUIT(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, uid);
							stm.setInt(2, cid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=rs;
								return obj;
							}});
			sucess=true;
		}
		return sucess;
	}

	@Override
	public int getIDByName(final String name) {
		// TODO Auto-generated method stub
		if(name!=null)
		{
			int id=(Integer) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL USER_GETIDBYNAME(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, name);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								if (rs.next())
								{
									return rs.getInt("id");
								}
								return 0;
							}});
			return id;
		}
		return 0;
	}

}
